﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Model
{
    public class UserManage
    {
        private DBUtil DB;
        public string strConn { get; set; }
        public int user_id { get; set; }
        public int user_type_id { get; set; }
        public string username { get; set; }
        public string password { get; set; }
        public string fname_th { get; set; }
        public string lname_th { get; set; }
        public string fname_en { get; set; }
        public string lname_en { get; set; }
        public string id_card { get; set; }
        public string birth_date { get; set; }
        public string face_image { get; set; }
        public string gender { get; set; }
        public string telephone { get; set; }
        public string mobile { get; set; }
        public string role { get; set; }
        public string lang { get; set; }
        public string create_date { get; set; }
        public int create_by { get; set; }
        public string update_date { get; set; }
        public int update_by { get; set; }
        public int site_id { get; set; }
        public string user_type_name_th { get; set; }
        public string user_type_name_en { get; set; }

        public UserManage()
        {
            this.DB = new DBUtil();
            DB.strConn = strConn;
            
            
        }

        public DataTable getByObject(UserManage obj)
        {

            DataTable dt = new DataTable();
            this.DB = new DBUtil();
            DB.strConn = strConn;

            string sql = "SELECT * FROM " +
                         "tcdc_mst_user u , tcdc_mst_usertype t " +
                         "WHERE u.user_type_id = t.user_type_id ";

            if (obj.user_id != null &&
               obj.user_id.ToString() != null &&
               obj.user_id.ToString() != "0")
            {
                sql += "AND user_id LIKE '%" + obj.user_id + "%' ";
            }
            if (obj.user_type_id != null &&
               obj.user_type_id.ToString() != null &&
               obj.user_type_id.ToString() != "0")
            {
                sql += "AND u.user_type_id LIKE '%" + obj.user_type_id + "%' ";
            }
            if (obj.username != null &&
               obj.username.ToString() != null &&
               obj.username.ToString() != "0")
            {
                sql += "AND username LIKE '%" + obj.username + "%' ";
            }
            if (obj.password != null &&
               obj.password.ToString() != null &&
               obj.password.ToString() != "0")
            {
                sql += "AND password LIKE '%" + obj.password + "%' ";
            }
            if (obj.fname_th != null &&
               obj.fname_th.ToString() != null &&
               obj.fname_th.ToString() != "0")
            {
                sql += "AND fname_th LIKE '%" + obj.fname_th + "%' ";
            }
            if (obj.lname_th != null &&
               obj.lname_th.ToString() != null &&
               obj.lname_th.ToString() != "0")
            {
                sql += "AND lname_th LIKE '%" + obj.lname_th + "%' ";
            }
            if (obj.fname_en != null &&
               obj.fname_en.ToString() != null &&
               obj.fname_en.ToString() != "0")
            {
                sql += "AND fname_en LIKE '%" + obj.fname_en + "%' ";
            }
            if (obj.lname_en != null &&
               obj.lname_en.ToString() != null &&
               obj.lname_en.ToString() != "0")
            {
                sql += "AND lname_en LIKE '%" + obj.lname_en + "%' ";
            }

            dt = DB.ExecuteReader(sql);

            return dt;

        }

        public int updateData(UserManage obj)
        {

            this.DB = new DBUtil();
            DB.strConn = strConn;

            SqlParameter[] prm;
            prm = new SqlParameter[21];

            prm[0] = new SqlParameter("@user_id", SqlDbType.Int);
            if (obj.user_id != null &&
               obj.user_id.ToString() != null &&
               obj.user_id.ToString() != "0")
            {
                prm[0].Value = obj.user_id;
            }
            else
            {
                prm[0].Value = DBNull.Value;
            }
            prm[1] = new SqlParameter("@user_type_id", SqlDbType.Int);
            if (obj.user_type_id != null &&
               obj.user_type_id.ToString() != null &&
               obj.user_type_id.ToString() != "0")
            {
                prm[1].Value = obj.user_type_id;
            }
            else
            {
                prm[1].Value = DBNull.Value;
            }
            prm[2] = new SqlParameter("@username", SqlDbType.NVarChar, 50);
            if (obj.username != null &&
               obj.username.ToString() != null &&
               obj.username.ToString() != "0")
            {
                prm[2].Value = obj.username;
            }
            else
            {
                prm[2].Value = DBNull.Value;
            }
            prm[3] = new SqlParameter("@password", SqlDbType.NVarChar, 50);
            if (obj.password != null &&
               obj.password.ToString() != null &&
               obj.password.ToString() != "0")
            {
                prm[3].Value = obj.password;
            }
            else
            {
                prm[3].Value = DBNull.Value;
            }
            prm[4] = new SqlParameter("@fname_th", SqlDbType.NVarChar, 250);
            if (obj.fname_th != null &&
               obj.fname_th.ToString() != null &&
               obj.fname_th.ToString() != "0")
            {
                prm[4].Value = obj.fname_th;
            }
            else
            {
                prm[4].Value = DBNull.Value;
            }
            prm[5] = new SqlParameter("@lname_th", SqlDbType.NVarChar, 50);
            if (obj.lname_th != null &&
               obj.lname_th.ToString() != null &&
               obj.lname_th.ToString() != "0")
            {
                prm[5].Value = obj.lname_th;
            }
            else
            {
                prm[5].Value = DBNull.Value;
            }
            prm[6] = new SqlParameter("@fname_en", SqlDbType.NVarChar, 250);
            if (obj.fname_en != null &&
               obj.fname_en.ToString() != null &&
               obj.fname_en.ToString() != "0")
            {
                prm[6].Value = obj.fname_en;
            }
            else
            {
                prm[6].Value = DBNull.Value;
            }
            prm[7] = new SqlParameter("@lname_en", SqlDbType.NVarChar, 50);
            if (obj.lname_en != null &&
               obj.lname_en.ToString() != null &&
               obj.lname_en.ToString() != "0")
            {
                prm[7].Value = obj.lname_en;
            }
            else
            {
                prm[7].Value = DBNull.Value;
            }
            prm[8] = new SqlParameter("@id_card", SqlDbType.NVarChar, 50);
            if (obj.id_card != null &&
               obj.id_card.ToString() != null &&
               obj.id_card.ToString() != "0")
            {
                prm[8].Value = obj.id_card;
            }
            else
            {
                prm[8].Value = DBNull.Value;
            }
            prm[9] = new SqlParameter("@birth_date", SqlDbType.DateTime);
            if (obj.birth_date != null &&
               obj.birth_date.ToString() != null &&
               obj.birth_date.ToString() != "0")
            {
                prm[9].Value = obj.birth_date;
            }
            else
            {
                prm[9].Value = DBNull.Value;
            }
            prm[10] = new SqlParameter("@face_image", SqlDbType.NVarChar, 250);
            if (obj.face_image != null &&
               obj.face_image.ToString() != null &&
               obj.face_image.ToString() != "0")
            {
                prm[10].Value = obj.face_image;
            }
            else
            {
                prm[10].Value = DBNull.Value;
            }
            prm[11] = new SqlParameter("@gender", SqlDbType.NVarChar, 1);
            if (obj.gender != null &&
               obj.gender.ToString() != null &&
               obj.gender.ToString() != "0")
            {
                prm[11].Value = obj.gender;
            }
            else
            {
                prm[11].Value = DBNull.Value;
            }
            prm[12] = new SqlParameter("@telephone", SqlDbType.NVarChar, 50);
            if (obj.telephone != null &&
               obj.telephone.ToString() != null &&
               obj.telephone.ToString() != "0")
            {
                prm[12].Value = obj.telephone;
            }
            else
            {
                prm[12].Value = DBNull.Value;
            }
            prm[13] = new SqlParameter("@mobile", SqlDbType.NVarChar, 50);
            if (obj.mobile != null &&
               obj.mobile.ToString() != null &&
               obj.mobile.ToString() != "0")
            {
                prm[13].Value = obj.mobile;
            }
            else
            {
                prm[13].Value = DBNull.Value;
            }
            prm[14] = new SqlParameter("@role", SqlDbType.NVarChar, 20);
            if (obj.role != null &&
               obj.role.ToString() != null &&
               obj.role.ToString() != "0")
            {
                prm[14].Value = obj.role;
            }
            else
            {
                prm[14].Value = DBNull.Value;
            }
            prm[15] = new SqlParameter("@lang", SqlDbType.NVarChar, 5);
            if (obj.lang != null &&
               obj.lang.ToString() != null &&
               obj.lang.ToString() != "0")
            {
                prm[15].Value = obj.lang;
            }
            else
            {
                prm[15].Value = DBNull.Value;
            }
            prm[16] = new SqlParameter("@create_date", SqlDbType.DateTime);
            if (obj.create_date != null &&
               obj.create_date.ToString() != null &&
               obj.create_date.ToString() != "0")
            {
                prm[16].Value = obj.create_date;
            }
            else
            {
                prm[16].Value = DBNull.Value;
            }
            prm[17] = new SqlParameter("@create_by", SqlDbType.Int);
            if (obj.create_by != null &&
               obj.create_by.ToString() != null &&
               obj.create_by.ToString() != "0")
            {
                prm[17].Value = obj.create_by;
            }
            else
            {
                prm[17].Value = DBNull.Value;
            }
            prm[18] = new SqlParameter("@update_date", SqlDbType.DateTime);
            if (obj.update_date != null &&
               obj.update_date.ToString() != null &&
               obj.update_date.ToString() != "0")
            {
                prm[18].Value = obj.update_date;
            }
            else
            {
                prm[18].Value = DBNull.Value;
            }
            prm[19] = new SqlParameter("@update_by", SqlDbType.Int);
            if (obj.update_by != null &&
               obj.update_by.ToString() != null &&
               obj.update_by.ToString() != "0")
            {
                prm[19].Value = obj.update_by;
            }
            else
            {
                prm[19].Value = DBNull.Value;
            }
            prm[20] = new SqlParameter("@site_id", SqlDbType.Int);
            if (obj.site_id != null &&
               obj.site_id.ToString() != null &&
               obj.site_id.ToString() != "0")
            {
                prm[20].Value = obj.site_id;
            }
            else
            {
                prm[20].Value = DBNull.Value;
            }

            return DB.ExecuteNonQueryProc("sp_tcdc_mst_user_update", prm, false);
        }

        public int deleteData(string id)
        {

            this.DB = new DBUtil();
            DB.strConn = strConn;

            SqlParameter[] prm;
            prm = new SqlParameter[1];

            prm[0] = new SqlParameter("@user_id", Convert.ToInt32(id));

            return DB.ExecuteNonQueryProc("sp_tcdc_mst_user_delete", prm, false);
        }
    }
}
